﻿using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ChinaDynastyTours.Models;
using System;

namespace ChinaDynastyTours.Controls
{
    class FlightOrderCtrl
    {
        #region 获取飞机相关信息
        /// <summary>
        /// 获取飞机预定表数据
        /// </summary>
        /// <param name="tourGroupScheduleId">行程计划ID,每条行程计划对应唯一一条飞机预订数据，反过来则不成立</param>
        /// <param name="expenseId">费用标识</param>
        /// <returns></returns>
        internal static DataTable GetFlightOrderData(int tourGroupScheduleId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘飞机预定信息’表联合数据
            string sql = string.Format(@"SELECT   t.[FlightOrderID],t.[ExpenseID],t.[TourgroupID],t.[TourgroupName],t.[TourGroupScheduleID],t.[FlightCode],t.[StartDate],t.[StartTime],
                                                  t.[EndDate],t.[EndTime],t.[BeginPosition],t.[EndPosition],t.[AccompanyNum],t.[AdultNum],t.[ChildNum],t.[BabyNum],t.[AdultUnitPrice], 
                                                  t.[ChildUnitPrice],t.[BabyUnitPrice],t.[AccompanyUnitPrice],t.[HandlingCharge],t.[Remark],t.[OrderState],t.[Seat],
                                                  e.[TypeBelong],e.[Status],e.[ProviderID],e.[ProviderName],e.[Budget],e.[ServiceName],e.[PayTotal],e.[Remark]as ExpenseRemark  
                                                  FROM [fee_FlightOrder] t,fee_Expense e WHERE  t.ExpenseID=e.ExpenseID AND t.TourGroupScheduleID={0}", tourGroupScheduleId);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        #endregion

        #region 飞机及飞机预订相关信息的增、删、改

        /// <summary>
        /// 创建飞机预订及费用数据
        /// </summary>
        /// <param name="expense"></param>
        /// <param name="flightOrder"></param>
        /// <returns></returns>
        internal static int CreateFlightOrderAndExpenseData(ExpenseEntity expense, FlightOrderEntity flightOrder)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlExpense = string.Format(@"INSERT INTO fee_Expense(
                                        [TourgroupID],
                                        [ProviderID],
                                        [ProviderName],
                                        [ServiceName],
                                        [Budget],
                                        [PayTotal],
                                        [ApplyPayTotal],
                                        [AdjustAmount],
                                        [Status],
                                        [TypeBelong],
                                        [ApplyPay],[PayStatus],[TourgroupName],[CreateByUserName],[TourGroupScheduleID],[CreateTime],[ExpenseTypeID],[ExpenseType])
                                        VALUES('{0}', {1}, '{2}', '{3}',{4},{5}, {6},{7}, '{8}', '{9}',{10},'{11}','{12}','{13}',{14},'{15}',{16},'{17}');Select SCOPE_IDENTITY();",
                                        expense.TourgroupID,
                                        expense.ProviderID,
                                        expense.ProviderName,
                                        expense.ServiceName,
                                        expense.Budget,
                                        expense.PayTotal,
                                        expense.ApplyPayTotal,
                                        expense.AdjustAmount,
                                        expense.Status,
                                        expense.TypeBelong,
                                        expense.ApplyPay,
                                        expense.PayStatus,
                                        expense.TourgroupName,
                                        expense.CreateByUserName,
                                        expense.TourGroupScheduleID,
                                        expense.CreateTime,
                                        expense.ExpenseTypeID,
                                        expense.ExpenseType);
            string expenseID = db.ExecuteScalar(CommandType.Text, sqlExpense).ToString();
            string sqlFlight = string.Format(@"INSERT INTO fee_FlightOrder (
                                        [ExpenseID], [TourgroupID], [TourgroupName],
                                        [TourGroupScheduleID], [FlightCode], [StartTime], [EndTime],
                                        [BeginPosition], [EndPosition], [AccompanyNum], [AdultNum], 
                                        [ChildNum], [BabyNum], [AdultUnitPrice], [ChildUnitPrice], 
                                        [BabyUnitPrice], [AccompanyUnitPrice], [HandlingCharge], [Remark],
                                        [StartDate],[EndDate],[OrderState],[ProviderID],[ProviderName],[Seat])
                                        VALUES({0},'{1}','{2}', {3},'{4}','{5}','{6}', '{7}', '{8}',{9},{10}, {11}, {12},{13},{14},{15},{16},{17},'{18}','{19}','{20}','{21}',{22},'{23}','{24}')",
                                       expenseID,
                                       expense.TourgroupID,
                                       flightOrder.TourgroupName,
                                       flightOrder.TourGroupScheduleID,
                                       flightOrder.FlightCode,
                                       flightOrder.StartTime,
                                       flightOrder.EndTime,
                                       flightOrder.BeginPosition,
                                       flightOrder.EndPosition,
                                       flightOrder.AccompanyNum,
                                       flightOrder.AdultNum,
                                       flightOrder.ChildNum,
                                       flightOrder.BabyNum,
                                       flightOrder.AdultUnitPrice,
                                       flightOrder.ChildUnitPrice,
                                       flightOrder.BabyUnitPrice,
                                       flightOrder.AccompanyUnitPrice,
                                       flightOrder.HandlingCharge,
                                       flightOrder.Remark,
                                       flightOrder.StartDate,
                                       flightOrder.EndDate,
                                       flightOrder.OrderState,
                                       //flightOrder.AccompanyQuantity,
                                       //flightOrder.AdultQuantity,
                                       //flightOrder.ChildQuantity,
                                       //flightOrder.BabyQuantity,
                                       flightOrder.ProviderID,
                                       flightOrder.ProviderName,
                                       flightOrder.Seat
                                       );
            return db.ExecuteNonQuery(CommandType.Text, sqlFlight);
        }
        /// <summary>
        /// 计调更新飞机预订的部分数据信息，不包括(TourgroupID,TourgroupName,OrderState)字段
        /// </summary>                           
        /// <param name="expense"></param>       
        /// <param name="flightOrder"></param>   
        /// <returns></returns>                  
        internal static int UpdateFlightOrderData(ExpenseEntity expense, FlightOrderEntity flightOrder)
        {                                        
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET 
                                        [ProviderID]={0},
                                        [ProviderName]='{1}',
                                        [ServiceName]='{2}',
                                        [Budget]={3}
                                        WHERE ExpenseID={4}",
                                       expense.ProviderID,
                                       expense.ProviderName,
                                       expense.ServiceName,
                                       expense.Budget,
                                       expense.ExpenseID);

            db.ExecuteNonQuery(CommandType.Text, sqlExpense);
            string sqlFlight = string.Format(@"UPDATE fee_FlightOrder SET
                                                [FlightCode]='{0}',[StartTime]='{1}', [EndTime]='{2}', [BeginPosition]='{3}',
                                                [EndPosition]='{4}',[AdultUnitPrice]={5},[ChildUnitPrice]={6}, [BabyUnitPrice]={7}, [AccompanyUnitPrice]={8},
                                                [HandlingCharge]={9}, [Remark]='{10}',[StartDate]='{11}',[EndDate]='{12}',
                                                [ProviderID]={13},[ProviderName]='{14}',[AccompanyNum]={15}, [AdultNum]={16},[ChildNum]={17}, [BabyNum]={18},[Seat]='{19}' 
                                                WHERE FlightOrderID={20}",//不被更新部分 [TourgroupID]='{0}', [TourgroupName]='{1}', [OrderState]='{12}',
                                        flightOrder.FlightCode,
                                        flightOrder.StartTime,
                                        flightOrder.EndTime,
                                        flightOrder.BeginPosition,
                                        flightOrder.EndPosition,
                                        flightOrder.AdultUnitPrice,
                                        flightOrder.ChildUnitPrice,
                                        flightOrder.BabyUnitPrice,
                                        flightOrder.AccompanyUnitPrice,
                                        flightOrder.HandlingCharge,
                                        flightOrder.Remark,
                                        flightOrder.StartDate,
                                        flightOrder.EndDate,
                                        //flightOrder.AccompanyQuantity,
                                        //flightOrder.AdultQuantity,
                                        //flightOrder.ChildQuantity,
                                        //flightOrder.BabyQuantity,
                                        flightOrder.ProviderID,
                                        flightOrder.ProviderName,
                                        //flightOrder.TourgroupID,
                                        //flightOrder.TourgroupName,
                                        flightOrder.AccompanyNum,
                                        flightOrder.AdultNum,
                                        flightOrder.ChildNum,
                                        flightOrder.BabyNum,
                                        flightOrder.Seat,
                                        //flightOrder.OrderState,
                                        flightOrder.FlightOrderID
                                    );
            return db.ExecuteNonQuery(CommandType.Text, sqlFlight);
        }

        /// <summary>
        /// 更改飞机的订单状态
        /// </summary>
        /// <param name="trainOrderId">订单号</param>
        /// <param name="orderState">状态</param>
        /// <returns></returns>
        internal static int UpdateFlightOrderStateData(string flightOrderId, OrderStateEnum orderState)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlTrain = string.Format(@"UPDATE fee_FlightOrder SET [OrderState]='{0}' WHERE FlightOrderID={1}", orderState, flightOrderId);

            return db.ExecuteNonQuery(CommandType.Text, sqlTrain);
        }

        /// <summary>
        /// 删除指定行程计划号的飞机预订及费用信息
        /// </summary>
        /// <param name="scheduleID"></param>
        /// <returns></returns>
        internal static int DeleteFlightOrder(int flightOrderId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sql = string.Format("select expenseid from fee_FlightOrder where FlightOrderID={0}", flightOrderId);
            DbCommand dbCmd = db.GetSqlStringCommand(sql);

            object expenseObj = db.ExecuteScalar(dbCmd);

            if (expenseObj == null)
                return 0;

            sql = string.Format(@"DELETE FROM fee_FlightOrder WHERE FlightOrderID={0};
                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用及其预订内容
                                         flightOrderId,
                                         expenseObj.ToString());
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }


        #endregion

        #region 飞机计调查询

        internal static DataTable GetFlightOrderData(string tourGroupId,string providerId, DateTime startDate, DateTime endDate, OrderStateEnum orderState)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘飞机预定信息’表联合数据

            string szCondition = "";

            if (tourGroupId != "")
                szCondition = "t.TourGroupID='" + tourGroupId + "'";
            if (szCondition != "")
            {
                if (providerId != "")
                    szCondition += " AND e.ProviderID='" + providerId + "'";
            }
            else
            {
                if (providerId != "")
                    szCondition += " e.ProviderID='" + providerId + "'";
            }
            if (szCondition != "")
            {
                if (orderState.ToString() == "全部")
                    szCondition += string.Format(" AND t.StartDate>='{0}' AND t.StartDate<='{1}' AND t.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
                else
                    szCondition += string.Format(" AND t.StartDate>='{0}' AND t.StartDate<='{1}' AND t.OrderState='{2}' AND t.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), orderState);
            }
            else
            {
                if (orderState.ToString() == "全部")
                    szCondition += string.Format(" t.StartDate>='{0}' AND t.StartDate<='{1}' AND t.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
                else
                    szCondition += string.Format(" t.StartDate>='{0}' AND t.StartDate<='{1}' AND t.OrderState='{2}' AND t.expenseid=e.expenseid ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"), orderState);
            }



            string Sql = @"SELECT t.[FlightOrderID],t.[ExpenseID],t.[TourgroupID],t.[TourgroupName],t.[TourGroupScheduleID],t.[FlightCode],t.[StartDate],t.[StartTime],
                                  t.[EndDate],t.[EndTime],t.[BeginPosition],t.[EndPosition],t.[AccompanyNum],t.[AdultNum],t.[ChildNum],t.[BabyNum],t.[AdultUnitPrice], 
                                  t.[ChildUnitPrice],t.[BabyUnitPrice],t.[AccompanyUnitPrice],t.[HandlingCharge],t.[Remark],t.[OrderState],t.[Seat],
                                  e.[Status],e.[ProviderID],e.[ProviderName],e.[Budget],e.[PayStatus],e.[ApplyPayTotal],e.[PayTotal] 
                        FROM [fee_FlightOrder] t,fee_Expense e ";

            if (szCondition != "")
                Sql += " WHERE " + szCondition;


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        
        /// <summary>
        /// 根据费用ID获取唯一飞机预订信息
        /// </summary>
        /// <param name="expenseId"></param>
        /// <returns></returns>
        internal static DataTable GetFlightOrderDataByExpenseId(string expenseId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            //根据行程计划ID，取得‘费用表’与‘宾馆预定信息’表联合数据
            string sql = string.Format(@"SELECT   t.[FlightOrderID],t.[ExpenseID],t.[TourgroupID],t.[TourgroupName],t.[TourGroupScheduleID],t.[FlightCode],t.[StartDate],t.[StartTime],
                                                  t.[EndDate],t.[EndTime],t.[BeginPosition],t.[EndPosition],t.[AccompanyNum],t.[AdultNum],t.[ChildNum],t.[BabyNum],t.[AdultUnitPrice], 
                                                  t.[ChildUnitPrice],t.[BabyUnitPrice],t.[AccompanyUnitPrice],t.[HandlingCharge],t.[Remark],t.[OrderState],t.[Seat],
                                                  e.[Status],e.[ProviderID],e.[ProviderName],e.[Budget] 
                                                  FROM [fee_FlightOrder] t,fee_Expense e   WHERE  t.ExpenseID =e.ExpenseID AND e.ExpenseID={0}", expenseId);

            dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion


    }
}
